import numpy as np
import pymysql
import pandas as pd
import pymysql.cursors


class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            database='scenic',
            port=3306,
            charset='utf8'
        )
    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT o.tourist_agency_name, u.id_no, LEFT(u.id_no, 2) as province_code,
        DAYOFWEEK(gate.create_time) as non_weekend,
        cast(substring(u.id_no, 7, 4) as unsigned) as birth_year
        FROM ticket_order_user_rel u JOIN ticket_order o on u.order_id = o.id JOIN order_user_gate_rel gate on gate.ticket_rel_id = u.id
        WHERE o.tourist_agency_name != '' and o.pay_time != ''
        """
    cursor.execute(sql)
    ret = cursor.fetchall()
    df = pd.DataFrame(ret)
    #print(df)

    df['non_weekend'] = df['non_weekend'].apply(lambda x:1 if x not in [1, 7] else 0)

    # 新增有效性标记列
    df['valid_id'] = df['id_no'].apply(lambda x : 1 if x and str(x).strip() != '' else 0)

    # 标记是否是老年人游客及外省游客
    df['elderly_ratio'] = df.apply(
        lambda x : 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan,
        axis=1
    )
    df['out_province_ratio'] = df.apply(
        lambda x : 1 if (x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else np.nan,
        axis=1
    )

    # 分组聚合计算占比
    result = df.groupby(['tourist_agency_name']).agg(
        total_visitors=('id_no', 'count'), # 总游客数
        valid_visitors=('valid_id', 'sum'), # 有效身份验证游客数
        out_province=('out_province_ratio', 'sum'), # 外省人数（仅有效id_no）
        elderly=('elderly_ratio', 'sum'), # 老年人人数（仅有效id_no）
        non_weekend_ratio=('non_weekend', 'mean'), # 非周末游玩占比(仅有效id_no)
    ).reset_index()

    # 计算实际比例
    result["out_province_ratio"] = result['out_province'] / result['valid_visitors'].replace(0, np.nan)
    result["elderly_ratio"] = result['elderly'] / result['valid_visitors'].replace(0, np.nan)

    # 删除中间列
    result = result.drop(['out_province', 'elderly'], axis=1)
    result["out_province_ratio"] = result["out_province_ratio"].fillna(0)
    result["elderly_ratio"] = result["elderly_ratio"].fillna(0)
    print(result.head())
    result.to_csv('scenic_data.csv')


if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()